package cc.magicjson.summary.errors.unique;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.*;
import org.apache.commons.csv.*;

@Slf4j
public class DruidSlowSqlProcessor {

    @Data
    static class SlowSqlRecord {
        private LocalDateTime timestamp;
        private String sql;
        private long executeTime;
        private String className;
        private String tid;
    }

    public void processDruidSlowSql(String inputPath, String outputPath) {
        try {
            List<SlowSqlRecord> records = readSlowSqlFile(inputPath);

            // 按SQL模式去重并保留最慢的记录（使用[xxx]之前的部分作为key）
            Map<String, SlowSqlRecord> uniqueRecords = new HashMap<>();
            for (SlowSqlRecord record : records) {
                String sql = record.getSql();
                String keyPart = extractSqlBeforeBracket(sql);  // 提取[xxx]之前的部分
                String normalizedSql = normalizeSql(keyPart);
                uniqueRecords.merge(normalizedSql, record,
                    (existing, replacement) -> existing.getExecuteTime() > replacement.getExecuteTime()
                        ? existing : replacement);
            }

            // 统计每种SQL模式的出现次数
            Map<String, Integer> sqlCounts = new HashMap<>();
            for (SlowSqlRecord record : records) {
                String sql = record.getSql();
                String keyPart = extractSqlBeforeBracket(sql);  // 提取[xxx]之前的部分
                String normalizedSql = normalizeSql(keyPart);
                sqlCounts.merge(normalizedSql, 1, Integer::sum);
            }

            // 按执行时间排序
            List<SlowSqlRecord> sortedRecords = new ArrayList<>(uniqueRecords.values());
            sortedRecords.sort((a, b) -> Long.compare(b.getExecuteTime(), a.getExecuteTime()));

            // 写入结果
            writeResults(sortedRecords, sqlCounts, outputPath);

        } catch (Exception e) {
            log.error("处理失败", e);
        }
    }

    private List<SlowSqlRecord> readSlowSqlFile(String filePath) throws IOException {
        List<SlowSqlRecord> records = new ArrayList<>();
        StringBuilder currentLog = new StringBuilder();
        Pattern headerPattern = Pattern.compile(
            "(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2},\\d{3}).*?\\[TID:(\\d+)\\].*?slow sql (\\d+) millis\\.");

        try (BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
            String line;
            SlowSqlRecord currentRecord = null;
            boolean isCollectingSql = false;
            StringBuilder sqlBuilder = new StringBuilder();

            while ((line = reader.readLine()) != null) {
                Matcher headerMatcher = headerPattern.matcher(line);

                if (headerMatcher.find()) {
                    // 保存前一条记录
                    if (currentRecord != null && sqlBuilder.length() > 0) {
                        String fullContent = sqlBuilder.toString();
                        int separatorIndex = fullContent.lastIndexOf("||");
                        if (separatorIndex != -1) {
                            currentRecord.setSql(fullContent.substring(0, separatorIndex).trim());
                            currentRecord.setClassName(fullContent.substring(separatorIndex + 2).trim());
                            records.add(currentRecord);
                        }
                    }

                    // 开始新记录
                    currentRecord = new SlowSqlRecord();
                    currentRecord.setTimestamp(LocalDateTime.parse(
                        headerMatcher.group(1),
                        DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss,SSS")));
                    currentRecord.setTid(headerMatcher.group(2));
                    currentRecord.setExecuteTime(Long.parseLong(headerMatcher.group(3)));

                    // 重置SQL构建器，并添加第一行SQL（从"millis."之后开始）
                    sqlBuilder.setLength(0);
                    String remainingLine = line.substring(headerMatcher.end());
                    sqlBuilder.append(remainingLine);

                } else if (currentRecord != null) {
                    // 继续收集SQL和类名
                    sqlBuilder.append("\n").append(line);
                }
            }

            // 处理最后一条记录
            if (currentRecord != null && sqlBuilder.length() > 0) {
                String fullContent = sqlBuilder.toString();
                int separatorIndex = fullContent.lastIndexOf("||");
                if (separatorIndex != -1) {
                    currentRecord.setSql(fullContent.substring(0, separatorIndex).trim());
                    currentRecord.setClassName(fullContent.substring(separatorIndex + 2).trim());
                    records.add(currentRecord);
                }
            }
        }

        return records;
    }

    private void writeResults(List<SlowSqlRecord> records, Map<String, Integer> sqlCounts,
            String outputPath) throws IOException {
        try (Writer writer = new OutputStreamWriter(Files.newOutputStream(Paths.get(outputPath)), StandardCharsets.UTF_8)) {
            // 写入BOM
            writer.write('\ufeff');

            CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
                .withHeader("执行时间(ms)", "出现次数", "最后执行时间", "类名", "SQL语句"));

            for (SlowSqlRecord record : records) {
                String normalizedSql = normalizeSql(record.getSql());
                csvPrinter.printRecord(
                    record.getExecuteTime(),
                    sqlCounts.get(normalizedSql),
                    record.getTimestamp().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss,SSS")),
                    record.getClassName(),
                    record.getSql().replaceAll("\\s+", " ")
                );
            }

            csvPrinter.flush();
        }
    }

    /**
     * 处理CSV字段中的特殊字符
     */
    private String escapeCSV(String field) {
        if (field == null) {
            return "";
        }
        // 如果字段包含逗号、双引号或换行符，需要用双引号包围
        // 字段中的双引号需要用两个双引号替换
        if (field.contains(",") || field.contains("\"") || field.contains("\n") || field.contains("\r")) {
            return "\"" + field.replace("\"", "\"\"") + "\"";
        }
        return field;
    }

    private String normalizeSql(String sql) {
        // 移除开头的"millis."（如果存在）
        sql = sql.replaceFirst("^\\s*millis\\.", "");

        return sql
            .replaceAll("'[^']*'", "?")           // 替换字符串字面量
            .replaceAll("\\b\\d+\\b", "?")        // 替换数字
            .replaceAll("IN\\s*\\([^)]+\\)", "IN (?)")  // 替换IN子句
            .trim()
            .toLowerCase();
    }

    private String generateSeparator(int length) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < length; i++) {
            sb.append("-");
        }
        return sb.toString();
    }

    // 新增方法：提取[xxx]之前的SQL部分
    private String extractSqlBeforeBracket(String sql) {
        if (sql == null) return "";
        int bracketIndex = sql.indexOf('[');
        return bracketIndex >= 0 ? sql.substring(0, bracketIndex).trim() : sql.trim();
    }
}
